package com.example.blockbreaker;

import androidx.lifecycle.LiveData;
import androidx.room.ColumnInfo;
import androidx.room.Dao;
import androidx.room.Delete;
import androidx.room.Insert;
import androidx.room.Query;
import androidx.room.Transaction;
import java.util.List;

@Dao
public interface CravingDao {


    /* ========== 基础操作 ========== */
    @Insert
    void insert(CravingRecord record);

    @Insert
    void insertAll(List<CravingRecord> records);

    @Delete
    void delete(CravingRecord record);
    @Query("DELETE FROM craving_records")
    void deleteAll();

    /* ========== 查询方法 ========== */
    @Query("SELECT * FROM craving_records ORDER BY timestamp DESC")
    LiveData<List<CravingRecord>> getAllRecords();

    @Query("SELECT * FROM craving_records WHERE id = :id LIMIT 1")
    CravingRecord getById(int id);

    /* ========== 带trigger字段的查询 ========== */
    @Query("SELECT * FROM craving_records " +
            "WHERE trigger IS NOT NULL AND trigger != '' " +
            "ORDER BY timestamp DESC")
    LiveData<List<CravingRecord>> getRecordsWithTriggers();

    // 修改返回类型
    @Query("SELECT trigger, COUNT(*) as count FROM craving_records " +
            "WHERE trigger IS NOT NULL AND trigger != '' " +
            "GROUP BY trigger ORDER BY count DESC LIMIT 5")
    LiveData<List<TriggerCount>> getTriggerStatistics();

    // 添加数据类
    class TriggerCount {
        @ColumnInfo(name = "trigger")
        public String trigger;

        @ColumnInfo(name = "count")
        public int count;
    }

    /* ========== 时间范围查询 ========== */

    @Query("SELECT * FROM craving_records " +
            "WHERE timestamp > :timestamp " +
            "ORDER BY timestamp DESC")
    LiveData<List<CravingRecord>> getRecordsAfter(long timestamp);

    @Query("SELECT COUNT(*) FROM craving_records " +
            "WHERE timestamp BETWEEN :start AND :end ")
    LiveData<Integer> getCountBetween(long start, long end);

    @Query("SELECT * FROM craving_records " +
            "WHERE timestamp BETWEEN :start AND :end " +
            "ORDER BY timestamp DESC")
    LiveData<List<CravingRecord>> getRecordsBetween(long start, long end);

    /* ========== 实用方法 ========== */
    @Query("SELECT COUNT(*) FROM craving_records " +
            "WHERE level >= :minLevel AND trigger LIKE '%' || :keyword || '%'")
    LiveData<Integer> countByLevelAndKeyword(int minLevel, String keyword);

    @Transaction
    default void updateTrigger(int id, String newTrigger) {
        // 先查询再更新
        CravingRecord record = getById(id);
        if (record != null) {
            record.trigger = newTrigger;
            insert(record); // Room会自动更新已存在记录
        }
    }
}